package tools;


import models.SqlReturnModel;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Objects;
import java.util.Properties;

/**
 * Created by yangxianming on 2015-06-19.
 * 数据操作都在这里
 */
public class OperateSql {
    //String JDBCURL = "jdbc:mysql://10.0.8.11:3109/housekeeper?useUnicode=true&characterEncoding=UTF-8";

    public OperateSql(Properties properties) {
        this.jdbcUrl = properties.getProperty("jdbcurl1");
        this.userName = properties.getProperty("userName");
        this.passWord = properties.getProperty("passWord");
        this.sqlName = properties.getProperty("sqlName");

    }

    private String jdbcUrl;
    private String userName;
    private String passWord;
    private String sqlName;
    private Statement statement;
    private Connection conn;
    private ResultSet resultSet;


    public void createConnection() throws Exception {
        if (jdbcUrl == null || jdbcUrl.equals("")) {
            throw new Exception("jdbcUrl没有值,数据库连接没有创建");
        }

        try {
            if (Objects.equals(sqlName, "mysql")) {
                Class.forName("com.mysql.jdbc.Driver"); //mysql
            }
            if (Objects.equals(sqlName, "sqlServer")) {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  //sqlServer
            }
            conn = DriverManager.getConnection(jdbcUrl, userName, passWord);

            this.statement = conn.createStatement();
        } catch (Exception se) {
            throw new Exception("数据库连接创建失败!请检查网络,config配置,用户名密码等");
        }
//        throw new Exception("数据库连接创建成功");
    }

    public SqlReturnModel executeSql(String sqlWord){
        try {
            if (statement == null || statement.isClosed())
                createConnection();
        }catch (Exception e){
            e.printStackTrace();
        }
        SqlReturnModel sqlReturnModel = new SqlReturnModel();

        try {
            if (Objects.equals(sqlWord.substring(0, sqlWord.indexOf(" ")).toUpperCase(), "SELECT")) {
                HashMap<String, ArrayList<String>> hashMap = new HashMap<String, ArrayList<String>>();
//                System.out.println("------" + sqlWord);
                resultSet = statement.executeQuery(sqlWord);

//                System.out.println(sqlWord + " || resultSet:" + resultSet.isClosed() + " ||statement:" + statement.isClosed());
                //获取列数
                int col = resultSet.getMetaData().getColumnCount();
                //定义返回容器
                for (int i = 1; i <= col; i++)
                    hashMap.put(String.valueOf(i), new ArrayList<String>());
                //向容器插入数据
                while (resultSet.next())
                    for (int i = 1; i <= col; i++)
                        hashMap.get(String.valueOf(i)).add(resultSet.getString(i));

                //将hashmap容器转换成arraylist
                int max = hashMap.get("1").size();
                for (int a = 0; a < max; a++) {   //列，0开头的数组
                    ArrayList<String> element = new ArrayList<String>();
                    for (int h = 0; h < hashMap.size(); h++) {  //行 1开头的hash
                        element.add(hashMap.get(String.valueOf(h + 1)).get(a));
                    }
                    sqlReturnModel.getSelectReturnData().add(element);
                }

            }
            if (Objects.equals(sqlWord.substring(0, sqlWord.indexOf(" ")).toUpperCase(), "UPDATE")) {
                statement.execute(sqlWord);
            }
            if (Objects.equals(sqlWord.substring(0, sqlWord.indexOf(" ")).toUpperCase(), "INSERT")) {
                statement.execute(sqlWord);
            }

        }catch (SQLException se) {
            se.printStackTrace();
            String errMessage = "sql exception: " + se.getMessage();
            System.out.println(errMessage);
            sqlReturnModel.setIsSuccess(false);
            sqlReturnModel.setFailingReason(errMessage);
        }finally {
            try {
                if (resultSet != null)
                    resultSet.close();
                statement.close();
                conn.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }

        return sqlReturnModel;
    }
}
